package com.ejuornal.service.impl;

import java.sql.Date;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Component;

import com.ejuornal.bean.Artical;
import com.ejuornal.bean.Page;
import com.ejuornal.dao.impl.ArticalDaoImpl;
import com.ejuornal.service.ArticalService;

@Component("articalService")
public class ArticalServiceImpl implements ArticalService {
	private ArticalDaoImpl articalDao;

	public void add(Artical artical) {
		String sql = "INSERT INTO t_artical (userId,boardId,title,cnAbstract,keywords,pubDate,viewCount,postDate,refDoc,status,enAbstract,content,perPeriodicalId) VALUES(?, ?, ?, ?, ?, ?, ?, now(), ?, ?, ?, ?, ?)";
		articalDao.add(
				sql,new Object[] { 
						artical.getUserId(), 
						artical.getBoardId(),
						artical.getTitle(),
						artical.getCnAbstract(), 
						artical.getKeywords(), 
						artical.getPubDate(), 
						0, 
						artical.getRefDoc(), 
						artical.getStatus(),
						artical.getEnAbstract(),
						artical.getContent(),
						artical.getPerPeriodicalId()
						});
	}

	public void check(int articalId, Date pubDate, String status) {
		String sql = "UPDATE t_artical SET pubDate = ?, status = ? WHERE articalId = ?";
		articalDao.update(sql, new Object[] { pubDate, status, articalId });
	}

	public void updatePeriodicalId(int articalId, int periodicalId) {
		String sql = "UPDATE t_artical SET periodicalId = ? WHERE articalId = ?";
		articalDao.update(sql, new Object[] { periodicalId, articalId });
	}

	public void updateViewCount(int articalId, int viewCount) {
		String sql = "UPDATE t_artical SET viewCount = ? WHERE articalId = ?";
		articalDao.update(sql, new Object[] { viewCount, articalId });
	}

	public void delete(int... articalId) {
		String sql = "DELETE FROM t_artical WHERE articalId = ?";
		articalDao.batchDelete(sql, articalId);
	}

	public Artical findById(int articalId) {
		return articalDao.findById(articalId);
	}

	public List<Artical> findTop(int count) {
		return articalDao.findTop(count);
	}

	public List<Artical> findByBoardId(int boardId, int periodicalId, int perPeriodicalId) {
		return articalDao.findByBoardId( boardId, periodicalId, perPeriodicalId);
	}

	public Page<Artical> findByStatus(int current_record, String url,
			String status) {
		String hsql = "WHERE status = ?";
		
		return articalDao.find(current_record, url, hsql, new Object[]{ status });
	}

	public Page<Artical> findAll(int current_record, String url) {
		return articalDao.find(current_record, url);
	}

	public ArticalDaoImpl getArticalDao() {
		return articalDao;
	}

	@Resource(name = "articalDao")
	public void setArticalDao(ArticalDaoImpl articalDao) {
		this.articalDao = articalDao;
	}

	@Override
	public Page<Artical> findByTitle(int current_record, String url,String status, String title) {
		String hsql = "WHERE status = ? and  title LIKE ? ";
		return articalDao.find(current_record, url, hsql, new Object[]{ status,"%" + title + "%" });
	}

    @Override
    public List<Artical> findByName(String name) {
        return articalDao.findByName(name);
    }

	@Override
	public void updateStatus(String status, int articalId) {
		// TODO Auto-generated method stub
		String hsql = "UPDATE t_artical SET status = ? WHERE articalId = ?";
		 articalDao.update(hsql, new Object[]{status,articalId});
		
	}

	@Override
	public Page<Artical> checkByStatusEditor(int current_record, String url,int userIdEditor, String status) {
	
		String  sqlcount =" SELECT DISTINCT count(*)  FROM t_artical  artical ,t_per_periodical   perper , t_periodical per ,t_editor  editor,t_user tuser WHERE  "+
		" artical.perPeriodicalId = perper.perPeriodicalId  AND  perper.periodicalId = per.periodicalId  AND  " +
		" per.periodicalId = editor.periodicalId AND   editor.userId = tuser.userId  AND  tuser.userId = ? AND artical.`status` = ? ";
		
		String  sql = " SELECT  DISTINCT artical.*, p.name FROM t_artical  artical ,t_per_periodical   perper , t_periodical p ,t_editor  editor,t_user tuser WHERE  " +
				" artical.perPeriodicalId = perper.perPeriodicalId  AND  perper.periodicalId = p.periodicalId  AND " +
				" p.periodicalId = editor.periodicalId AND   editor.userId = tuser.userId  AND  tuser.userId = ? AND artical.`status` = ?   limit " + current_record + ", 10";
		
		return articalDao.find(current_record, url, sqlcount, sql, new Object[]{userIdEditor,status});
	}

	@Override
	public Page<Artical> checkByStatusCommit(int current_record, String url,int userIdCommit, String status){
		
	
	String sqlCount2 = " SELECT COUNT(DISTINCT artical.articalId )  FROM t_artical  artical ,t_per_periodical   perper , t_periodical p ,t_editor_commit  ecommit  WHERE " + 
			 "  artical.perPeriodicalId = perper.perPeriodicalId  AND  perper.periodicalId = p.periodicalId  AND "+
		  "   p.periodicalId = ecommit.periodicalId AND   ecommit.userId  = ? AND artical.`status` = ? AND  " + 
		    " artical.articalId  NOT IN (SELECT t_check.articalId FROM t_check WHERE  t_check.userId =? )  ";
	
	
	
	String  sql2="SELECT DISTINCT artical.* ,p.name FROM t_artical  artical ,t_per_periodical   perper , t_periodical p ,t_editor_commit  ecommit  WHERE " + 
					 "  artical.perPeriodicalId = perper.perPeriodicalId  AND  perper.periodicalId = p.periodicalId  AND "+
				  "   p.periodicalId = ecommit.periodicalId AND   ecommit.userId  = ? AND artical.`status` = ? AND  " + 
				    " artical.articalId  NOT IN (SELECT t_check.articalId FROM t_check WHERE  t_check.userId =? )  " +
				    " limit " + current_record + ", 10 ";
		
		// TODO Auto-generated method stub
		return articalDao.find(current_record, url, sqlCount2, sql2, new Object[]{userIdCommit,status,userIdCommit});
	}

	@Override
	public Page<Artical> checkByStatusChief(int current_record, String url,int userIdChief, String status) {
		
		String sql = "SELECT DISTINCT artical.*,p.name FROM t_artical AS artical INNER JOIN t_per_periodical AS perper ON artical.perPeriodicalId = perper.perPeriodicalId " +
				"INNER JOIN t_periodical AS p ON perper.periodicalId = p.periodicalId" +
				" INNER JOIN t_editor_chief AS ec ON p.userId = ec.userId WHERE ec.userId = ? AND artical.`status` = ?  ";
		String sqlCount="SELECT DISTINCT COUNT(*) FROM t_artical AS artical INNER JOIN t_per_periodical AS perper ON artical.perPeriodicalId = perper.perPeriodicalId " +
				"INNER JOIN t_periodical AS p ON perper.periodicalId = p.periodicalId" +
				" INNER JOIN t_editor_chief AS ec ON p.userId = ec.userId WHERE ec.userId = ? AND artical.`status` = ?  limit " + current_record + ", 10";
		return articalDao.find(current_record, url, sqlCount, sql, new Object[]{userIdChief,status});
	}
	
	@Override
	public Page<Artical> checkEditorLast(int current_record, String url,
			int userIdEditor, String status) {
		// TODO Auto-generated method stub

		/*String  sqlcount =" SELECT DISTINCT count(*)  FROM t_artical  artical ,t_per_periodical   perper , t_periodical per ,t_editor  editor,t_user tuser WHERE  "+
		" artical.perPeriodicalId = perper.perPeriodicalId  AND  perper.periodicalId = per.periodicalId  AND  " +
		" per.periodicalId = editor.periodicalId AND   editor.userId = tuser.userId  AND  tuser.userId = ? AND artical.`status` = ? ";*/
		String  sqlcount = "  SELECT DISTINCT count(*)  FROM  t_artical artical , t_article_editor ae WHERE " +
			      "  artical.articalId = ae.articalId AND ae.userId = ? AND artical.`status` = ? ";
		/*String  sql = " SELECT  DISTINCT artical.*, p.name FROM t_artical  artical ,t_per_periodical   perper , t_periodical p ,t_editor  editor,t_user tuser WHERE  " +
				" artical.perPeriodicalId = perper.perPeriodicalId  AND  perper.periodicalId = p.periodicalId  AND " +
				" p.periodicalId = editor.periodicalId AND   editor.userId = tuser.userId  AND  tuser.userId = ? AND artical.`status` = ?   limit " + current_record + ", 10";*/
		String  sql = "  SELECT DISTINCT artical.* FROM  t_artical artical , t_article_editor ae WHERE " +
				      "  artical.articalId = ae.articalId AND ae.userId = ? AND artical.`status` = ? limit " + current_record + ", 10";
		return articalDao.findArtical(current_record, url,10, sqlcount, sql, new Object[]{userIdEditor,status});
	}

	@Override
	public void updateAtical(Artical artical) {
		// TODO Auto-generated method stub
		String  sql="UPDATE  t_artical a  SET a.content = ? ,a.status = ?, a.pubDate=? WHERE a.articalId = ? ";
		
		articalDao.update(sql, new Object []{
				artical.getContent(),
				artical.getStatus(),
				artical.getPubDate(),
				artical.getArticalId()
		});
	}


	@Override
	public Page<Artical> findMyArtical(int current_record, String url, int userId, String status) {
		String sqlCount = "SELECT COUNT(*) FROM t_periodical p, t_per_periodical per, t_artical a " +
				"WHERE a.perPeriodicalId = per.perPeriodicalId AND per.periodicalId = p.periodicalId  AND a.userId = ? AND a.status = ?";
		String sql = "SELECT a.*, p.name FROM t_periodical p, t_per_periodical per, t_artical a " +
				"WHERE a.perPeriodicalId = per.perPeriodicalId AND per.periodicalId = p.periodicalId  AND a.userId = ? AND a.status = ? LIMIT " + current_record + ", 10";
		
		return articalDao.find(current_record, url, sqlCount, sql, new Object[]{ userId,status });
	}

	@Override
	public Page<Artical> findCheckingArtical(int current_record, String url, int userId ) {
		String sqlCount = "SELECT COUNT(*) FROM t_periodical p, t_per_periodical per, t_artical a " +
				"WHERE a.perPeriodicalId = per.perPeriodicalId AND per.periodicalId = p.periodicalId  AND a.userId = ? AND a.status IN (1,4,5,6,7)";
		String sql = "SELECT a.*, p.name FROM t_periodical p, t_per_periodical per, t_artical a " +
				"WHERE a.perPeriodicalId = per.perPeriodicalId AND per.periodicalId = p.periodicalId  AND a.userId = ? AND a.status IN (1,4,5,6,7) LIMIT " + current_record + ", 10";
		return articalDao.find(current_record, url, sqlCount, sql, new Object[]{ userId });
	}
	 public List<Artical> findByPerPeriodical(int id) {
	     return articalDao.findByPerPeriodical(id);
	 }
}
